using IRepository;
using IServices;
using Microsoft.AspNetCore.Hosting;
using NPOI.SS.Formula.Functions;
using NPOI.XSSF;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using YL.Core.Dto;
using YL.Core.Entity;
using YL.Utils.Excel;
using YL.Utils.Extensions;
using YL.Utils.Files;
using YL.Utils.Json;
using YL.Utils.Log;
using YL.Utils.Pub;
using YL.Utils.Table;

namespace Services
{
    public class Wms_stockinServices : BaseServices<Wms_stockin>, IWms_stockinServices
    {
        private readonly IWms_stockinRepository _repository;
        private readonly IWms_stockindetailRepository _detail;
        private readonly SqlSugarClient _client;
        private readonly IWms_inventoryRepository _inventory;
        private readonly IWms_inventoryrecordRepository _inventoryrecord;
        private readonly IHostingEnvironment _env;
        private readonly IWms_materialRepository _material;
        private readonly ILogUtil _log;

        public Wms_stockinServices(
            SqlSugarClient client,
            IWms_inventoryRepository inventoryRepository,
            IWms_inventoryrecordRepository inventoryrecordRepository,
            IWms_stockindetailRepository detail,
            IHostingEnvironment env,
            IWms_stockinRepository repository,
            IWms_materialRepository mat,
            ILogUtil log) : base(repository)
        {
            _client = client;
            _repository = repository;
            _detail = detail;
            _inventory = inventoryRepository;
            _inventoryrecord = inventoryrecordRepository;
            _env = env;
            _material = mat;
            this._log = log;
        }

        public bool Auditin(long UserId, long stockInId)
        {
            throw new NotImplementedException();
        }

        public string PageList(PubParams.StockInBootstrapParams bootstrap)
        {
            int totalNumber = 0;
            if (bootstrap.offset != 0)
            {
                bootstrap.offset = bootstrap.offset / bootstrap.limit + 1;
            }
            var query = _client.Queryable<Wms_stockin, Wms_material, Wms_supplier, Sys_user, Sys_user>
                   ((s, p, m, c, u) => new object[] {
                   JoinType.Left,s.MaterialId==p.MaterialId,
                   JoinType.Left,s.SupplierId==m.SupplierId,
                   JoinType.Left,s.CreateBy==c.UserId,
                   JoinType.Left,s.ModifiedBy==u.UserId,
                    })
                    .Where((s, p, m, c, u) => s.IsDel == 1)
                    .Select((s, p, m, c, u) => new
                    {
                        StockInId = s.StockInId.ToString(),
                        s.SupplierId,
                        s.MaterialId,
                        s.StockType,
                        p.MaterialName,
                        p.Unit,
                        m.SupplierName,
                        s.Weight,
                        s.IsDel,
                        s.CcNo,
                        s.Remark,
                        s.Go,
                        s.Total,
                        s.Price,
                        CName = c.UserName,
                        s.CreateDate,
                        UName = u.UserName,
                        s.ModifiedDate
                    }).MergeTable();

            query = query.Where(s => s.StockType == bootstrap.StockType);

            if (!bootstrap.search.IsEmpty())
            {
                query.Where((s) => s.SupplierName.Contains(bootstrap.search) || s.CName.Contains(bootstrap.search) || s.MaterialName.Contains(bootstrap.search));
            }
            if (!bootstrap.datemin.IsEmpty())
            {
                query.Where(s => s.CreateDate > bootstrap.datemin.ToDateTimeB());
            }

            if (!bootstrap.datemax.IsEmpty())
            {
                query.Where(s => s.CreateDate <= bootstrap.datemax.ToDateTimeE());
            }

            if (!string.IsNullOrEmpty(bootstrap.SupplierId) && bootstrap.SupplierId != "-1")
            {
                query.Where(s => s.SupplierId == Convert.ToInt64(bootstrap.SupplierId));
            }

            if (bootstrap.MaterialId != "-1")
            {
                query.Where(s => s.MaterialId == Convert.ToInt64(bootstrap.MaterialId));
            }

            if (!bootstrap.UserName.IsEmpty())
            {
                query.Where(x => x.CName.Contains(bootstrap.UserName));
            }

            if (bootstrap.order.Equals("desc", StringComparison.OrdinalIgnoreCase))
            {
                query.OrderBy($"MergeTable.{bootstrap.sort} desc");
            }
            else
            {
                query.OrderBy($"MergeTable.{bootstrap.sort} asc");
            }
            var list = query.ToPageList(bootstrap.offset, bootstrap.limit, ref totalNumber);
            return Bootstrap.GridData(list, totalNumber).JilToJson();
        }

        public string PrintList(string stockInId)
        {
            var list1 = _client.Queryable<Wms_stockin, Wms_supplier, Sys_user, Sys_user>
                ((s, p, c, u) => new object[] {
                   JoinType.Left,s.SupplierId==p.SupplierId,
                   JoinType.Left,s.CreateBy==c.UserId,
                   JoinType.Left,s.ModifiedBy==u.UserId,
                 })
                 .Where((s, p, c, u) => s.IsDel == 1)
                 .Select((s, p, c, u) => new
                 {
                     StockInId = s.StockInId.ToString(),
                     s.SupplierId,
                     p.SupplierName,
                     s.IsDel,
                     s.Remark,
                     s.Price,
                     s.Go,
                     s.Total,
                     CName = c.UserName,
                     s.CreateDate,
                     UName = u.UserName,
                     s.ModifiedDate
                 }).MergeTable().Where(s => s.StockInId == stockInId).ToList();
            bool flag1 = true;
            bool flag2 = true;
            var list2 = _client.Queryable<Wms_stockindetail, Wms_material, Wms_stockin, Wms_storagerack, Sys_user, Sys_user, Sys_user>
                 ((s, m, p, g, c, u, a) => new object[] {
                   JoinType.Left,s.MaterialId==m.MaterialId,
                   JoinType.Left,s.StockInId==p.StockInId,
                   JoinType.Left,s.StoragerackId==g.StorageRackId,
                   JoinType.Left,s.CreateBy==c.UserId,
                   JoinType.Left,s.ModifiedBy==u.UserId,
                   JoinType.Left,s.AuditinId==a.UserId,
                  })
                  .Where((s, m, p, g, c, u, a) => s.IsDel == 1 && p.IsDel == 1 && g.IsDel == 1 && c.IsDel == 1)
                  .Select((s, m, p, g, c, u, a) => new
                  {
                      StockInId = s.StockInId.ToString(),
                      StockInDetailId = s.StockInDetailId.ToString(),
                      m.MaterialName,
                      g.StorageRackNo,
                      g.StorageRackName,
                      Status = SqlFunc.IF(s.Status == 1).Return(StockInStatus.initial.GetDescription())
                      .ElseIF(s.Status == 2).Return(StockInStatus.egis.GetDescription())
                      .ElseIF(s.Status == 3).Return(StockInStatus.auditfailed.GetDescription())
                      .End(StockInStatus.underReview.GetDescription()),
                      s.PlanInQty,
                      s.ActInQty,
                      s.IsDel,
                      s.Remark,
                      s.AuditinTime,
                      AName = a.UserNickname,
                      CName = c.UserNickname,
                      s.CreateDate,
                      UName = u.UserNickname,
                      s.ModifiedDate
                  }).MergeTable().Where(c => c.StockInId == stockInId).OrderBy(c => c.CreateDate, OrderByType.Desc).ToList();
            if (!list1.Any())
            {
                flag1 = false;
            }
            if (!list2.Any())
            {
                flag2 = false;
            }
            var html = FileUtil.ReadFileFromPath(Path.Combine(_env.WebRootPath, "upload", "StockIn.html"));
            return (flag1, list1, flag2, list2, html).JilToJson();
        }


        /// <summary>
        /// 统计信息
        /// </summary>
        /// <param name="bootParams"></param>
        /// <returns></returns>
        public List<InventoryCollect> GetSumInventory(PubParams.StockInSummaryBootstrapParams bootParams)
        {
            var mAllList = this._client.Queryable<Wms_material>().ToList();

            var allMaterialIdList = new List<long?>();
            foreach (var item in mAllList)
            {
                allMaterialIdList.Add(item.MaterialId);
            }

            var query = _client.Queryable<Wms_stockin>().Where(x => x.IsDel == 1 && x.StockType == bootParams.StockType && allMaterialIdList.Contains(x.MaterialId));

            if (!bootParams.datemin.IsEmpty())
            {
                query.Where(s => s.CreateDate > bootParams.datemin.ToDateTimeB());
            }

            if (!bootParams.datemax.IsEmpty())
            {
                query.Where(s => s.CreateDate <= bootParams.datemax.ToDateTimeE());
            }

            var inList = query.ToList();

            var groupInfo = inList.GroupBy(x => x.MaterialId).Select(g => new { MaterialId = g.Key, Weight = g.Sum(x => x.Weight), Total = g.Sum(x => x.Total) });

            var mList = mAllList.Where(x => x.IsDel == 1).ToList();

            List<InventoryCollect> collectList = new List<InventoryCollect>();
            foreach (var item in groupInfo)
            {
                var mItem = mList.Where(x => x.MaterialId == item.MaterialId).First();
                var collectItem = new InventoryCollect();
                collectItem.MaterialId = item.MaterialId.ToString();
                collectItem.MaterialName = mItem.MaterialName;
                collectItem.Weight = item.Weight;
                collectItem.Unit = mItem.Unit;
                collectItem.Total = item.Total;

                collectList.Add(collectItem);
            }
            collectList.OrderByDescending(x => x.MaterialId);

            return collectList;
        }

        /// <summary>
        /// ��ǰ���������Ϣ
        /// </summary>
        /// <returns></returns>
        public List<StockInfo> GetStockInfoHtml()
        {
            var pankuPatch = this._client.Queryable<Wms_inventoryPatch>().OrderBy("CreateDate desc").First();

            if (pankuPatch == null)
            {
                return new List<StockInfo>();
            }

            var pankuInfo = this._client.Queryable<Wms_inventory>().Where(x => x.PatchId == pankuPatch.PatchId && x.IsDel == 1).ToList();

            var stockInInfo = this._client.Queryable<Wms_stockin>().Where(x => x.CreateDate > pankuPatch.CreateDate).ToList();

            var stockAddInfo = stockInInfo.Where(x => x.IsDel == 1 && x.StockType == 0)
                .GroupBy(x => x.MaterialId)
                .Select(g => new { mId = g.Key.ToString(), mVal = g.Sum(x => x.Weight) })
                .ToList();

            var stockMinusInfo = stockInInfo.Where(x => x.IsDel == 1 && x.StockType == 1)
                .GroupBy(x => x.MaterialId)
                .Select(g => new { mId = g.Key.ToString(), mVal = g.Sum(x => x.Weight) })
                .ToList();

            var materialInfo = this._client.Queryable<Wms_material>().Where(x => x.IsDel == 1).ToList();

            var stockList = new List<StockInfo>();
            foreach (var item in pankuInfo)
            {
                string mId = item.MaterialId.ToString();
                string mName = item.MaterialName.ToString();

                double curVal = item.Weight;

                var addItem = stockAddInfo.Where(x => x.mId == mId).FirstOrDefault();
                double addVal = 0;
                if (addItem != null)
                {
                    addVal = addItem.mVal;
                }

                var minusItem = stockMinusInfo.Where(x => x.mId == mId).FirstOrDefault();
                double minusVal = 0;
                if (minusItem != null)
                {
                    minusVal = minusItem.mVal;
                }

                double totalVal = curVal + addVal - minusVal;

                var stockItem = new StockInfo();
                stockItem.MaterialId = mId;
                stockItem.MaterialName = mName;
                stockItem.WeightAdd = addVal;
                stockItem.WeightMinus = minusVal;
                stockItem.WeightTotal = totalVal;
                stockItem.WeightInnit = curVal;

                stockList.Add(stockItem);
            }

            var panMList = pankuInfo.Select(x => x.MaterialId);
            var notPanMList = materialInfo.Where(x => !panMList.Contains(x.MaterialId)).ToList();
            foreach (var item in notPanMList)
            {
                string mId = item.MaterialId.ToString();
                string mName = item.MaterialName.ToString();

                double curVal = 0;

                var addItem = stockAddInfo.Where(x => x.mId == mId).FirstOrDefault();
                double addVal = 0;
                if (addItem != null)
                {
                    addVal = addItem.mVal;
                }

                var minusItem = stockMinusInfo.Where(x => x.mId == mId).FirstOrDefault();
                double minusVal = 0;
                if (minusItem != null)
                {
                    minusVal = minusItem.mVal;
                }

                double totalVal = curVal + addVal - minusVal;

                var stockItem = new StockInfo();
                stockItem.MaterialId = mId;
                stockItem.MaterialName = mName;
                stockItem.WeightAdd = addVal;
                stockItem.WeightMinus = minusVal;
                stockItem.WeightTotal = totalVal;
                stockItem.WeightInnit = curVal;

                stockList.Add(stockItem);
            }


            return stockList;
        }

        public StockInfoTableNew GetTableStockIn(string dateMin, string dateMax, int stockType = 0)
        {
            var mList = new List<Wms_material>();

            if (stockType == 0)  // 0入库 1 出库
            {
                mList = this._client.Queryable<Wms_material>().Where(x => x.IsDel == 1 && (x.Source == 0 || x.Source == 1)).OrderBy(x => x.InOrderNo).ToList();
            }
            else
            {
                mList = this._client.Queryable<Wms_material>().Where(x => x.IsDel == 1 && (x.Source == 0 || x.Source == 2)).OrderBy(x => x.OutOrderNo).ToList();
            }



            List<long?> mIds = new List<long?>();
            foreach (var item in mList)
            {
                mIds.Add(item.MaterialId);
            }

            DateTime beginDt = DateTimeExt.ToDateTimeB(dateMin);
            DateTime endDt = DateTimeExt.ToDateTimeE(dateMax);

            // StockIn，获取物料表中出现过的数据
            var stockList = this._client.Queryable<Wms_stockin>().Where(x => x.IsDel == 1 && x.CreateDate > beginDt && x.CreateDate < endDt && x.StockType == stockType && mIds.Contains(x.MaterialId)).ToList();


            // 生成表头
            List<string> titleList = new List<string>();
            List<string> titleBm = new List<string>();
            titleList.Add("车船号");
            titleBm.Add("CcNo");

            foreach (var item in mList)
            {
                titleList.Add(item.MaterialName);
                titleBm.Add(item.MaterialId.ToString());
            }
            titleList.Add("单价");
            titleBm.Add("Price");
            titleList.Add("计价");
            titleBm.Add("Total");
            titleList.Add("去向");
            titleBm.Add("Go");
            titleList.Add("备注");
            titleBm.Add("Remark");

            var titleLen = titleList.Count();

            var matLen = mList.Count();

            var dataList = new List<StockInfoTableRow>();

            var index = 0;
            foreach (var item in mList)
            {
                var mData = stockList.Where(x => x.MaterialId == item.MaterialId).ToList();

                if (mData != null && mData.Count > 0)
                {
                    foreach (var mItem in mData)
                    {
                        string rowId = mItem.StockInId.ToString();
                        string[] dataArray = new string[titleLen];
                        dataArray[0] = mItem.CcNo;
                        dataArray[index + 1] = mItem.Weight.ToString();
                        dataArray[matLen + 1] = mItem.Price.ToString();
                        dataArray[matLen + 2] = mItem.Total.ToString();
                        dataArray[matLen + 3] = mItem.Go;
                        dataArray[matLen + 4] = mItem.Remark;
                        List<string> tmpDataList = dataArray.ToList();

                        dataList.Add(new StockInfoTableRow() { RowId = rowId, Data = tmpDataList });
                    }
                }
                index++;
            }

            var ret = new StockInfoTableNew();
            ret.Title = titleList;
            ret.DataList = dataList;
            ret.TitleBm = titleBm;
            return ret;
        }


        public byte[] ExportList(string dateMin, string dateMax, int stockType = 0)
        {
            var stockRet = GetTableStockIn(dateMin, dateMax, stockType);
            List<List<string>> dataInfo = new List<List<string>>();
            foreach (var item in stockRet.DataList)
            {
                dataInfo.Add(item.Data);
            }
            var buffer = NpoiUtil.ExportList(dataInfo, stockRet.Title, ExcelVersion.V2007);
            return buffer;
        }

        public string GetStockInList(string dateMin, string dateMax, string materialId, int stockType = 0)
        {
            var mId = Int64.Parse(materialId);
            DateTime beginTime = dateMin.ToDateTimeB();
            DateTime endTime = dateMax.ToDateTimeE();
            var mList = this._client.Queryable<Wms_stockin>().Where(x => x.CreateDate > beginTime && x.CreateDate < endTime && x.IsDel == 1 && x.StockType == stockType && x.MaterialId == mId).ToList();
            var mJson = mList.JilToJson();
            return mJson;
        }

        public (bool, string) Import(System.Data.DataTable dt, long userId, int stockType)
        {
            var cols = dt.Columns;
            var matList = new List<string>();
            for (int i = 1; i < cols.Count - 4; i++)
            {
                string colName = cols[i].ColumnName.Replace("（", "(");
                // excel表中物料名称可能存在空格，此处将这些信息剔除，方便与material表中的数据做对比
                if (colName.IndexOf('(') > 0)
                {
                    colName = colName.Substring(0, colName.IndexOf('('));
                }
                colName = colName.Replace(" ", "");
                matList.Add(colName);
            }
            int matCount = matList.Count;
            var materialList = this._client.Queryable<Wms_material>().Where(x => matList.Contains(x.MaterialName)).ToList();

            DateTime impDate = DateTime.Now;
            var lastUploadInfo = this._client.Queryable<Wms_Upload>().Where(x => x.CreateBy == userId).OrderBy("CreateDate desc").First();
            if (lastUploadInfo != null)
            {
                impDate = DateTime.Parse(lastUploadInfo.UploadDate + " " + impDate.ToString("HH:mm:ss"));
            }

            List<Wms_stockin> stockInList = new List<Wms_stockin>();
            try
            {
                for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
                {
                    int i = 0;
                    var tempRow = dt.Rows[rowIndex];
                    double matVal = 0;
                    long matId = 0;
                    int k = 0;
                    for (k = 1; k < matCount + 1; k++)
                    {
                        string tmpVal = tempRow[k].ToString();

                        if (!string.IsNullOrEmpty(tmpVal) && double.TryParse(tmpVal, out matVal))
                        {
                            string tmpMatName = matList[k - 1];
                            matId = materialList.Where(x => x.MaterialName == tmpMatName).Select(x => x.MaterialId).FirstOrDefault();
                            break;
                        }
                    }
                    if (matVal > 0)
                    {
                        Wms_stockin stockInItem = new Wms_stockin();
                        stockInItem.CcNo = tempRow[0].ToString();
                        stockInItem.StockInId = PubId.SnowflakeId;
                        stockInItem.MaterialId = matId;
                        stockInItem.Weight = matVal;
                        var priceStr = tempRow[matCount + 1].ToString().Trim();
                        stockInItem.Price = string.IsNullOrEmpty(priceStr) ? 0 : double.Parse(priceStr);
                        var totalStr = tempRow[matCount + 2].ToString().Trim();
                        stockInItem.Total = string.IsNullOrEmpty(totalStr) ? 0 : double.Parse(priceStr);
                        stockInItem.Go = tempRow[matCount + 3].ToString();
                        stockInItem.Remark = tempRow[matCount + 4].ToString();
                        stockInItem.CreateBy = userId;
                        stockInItem.CreateDate = impDate;
                        stockInItem.StockType = stockType;

                        stockInList.Add(stockInItem);
                    }
                }

                int rowCount = this._client.Insertable(stockInList.ToArray()).ExecuteCommand();
            }
            catch (Exception ex)
            {
                return (false, string.Empty);
            }

            return (true, string.Empty);
        }

        public string WriteUploadInfo(string uploadDate, int stockType, long userId)
        {
            Wms_Upload uploadItem = new Wms_Upload();
            uploadItem.UploadId = PubId.SnowflakeId;
            uploadItem.CreateBy = userId;
            uploadItem.UploadDate = uploadDate;
            uploadItem.StockType = stockType;

            this._client.Insertable<Wms_Upload>(uploadItem).ExecuteCommand();
            return "成功";
        }
    }
}